/**
 * 导出带格式的excel
 */
const path = require('path');
const fs = require('fs');
const moment = require('moment');
const ExcelJS = require('exceljs');
const sizeOf = require('image-size');
const shortid = require('shortid');
const tools = require('./common');

/**
 * 根据表字段定义取数据
 * @param {Object} row 数据行
 * @param {String} field 字段名
 */
function getValue (row, field) {
  const regex_normal = /\{(.+?)\}/g; // {}花括号定义为普通字段值
  const regex_img = /\[(.+?)\]/g; // []方括号定义为图片
  const regex_date = /<(.+?)>/g; // <>尖括号定义为日期

  const fields = field.match(regex_normal) || [];
  const images = field.match(regex_img) || [];
  const dates = field.match(regex_date) || [];

  let value = field;
  if (fields.length === 0 && images.length === 0 && dates.length === 0 && field.length > 0) { // 普通定义字段
    value = row[field] === null || row[field] === undefined ? '' : row[field];
  }

  // 组合字段替换普通字段
  fields.forEach(v => {
    let fv = row[v.substring(1, v.length - 1)];
    if (fv === undefined || fv === null) {
      fv = '';
    }
    const reg = new RegExp(v, 'g');
    value = value.replace(reg, fv);
  })
  // 替换日期字段
  dates.forEach(v => {
    let fv = row[v.substring(1, v.length - 1)];
    if (fv === undefined || fv === null) {
      fv = '';
    }
    const reg = new RegExp(v, 'g');
    let date = fv && moment(fv * 1000).format('YYYY/MM/DD');
    if (date === 'Invalid date') date = fv;
    value = value.replace(reg, date);
  });
  // 替换图片
  const rootFolder = getRootPath();
  let defaultImgDir = '';
  if (process.env.NODE_ENV === 'development') { // 开发环境
    defaultImgDir = path.join(path.resolve(), 'public');
  } else { // 生产环境
    defaultImgDir = path.join(path.resolve(), 'www');
  }

  const imgObj = [];
  images.forEach(v => {
    const fv = row[v.substring(1, v.length - 1)] || '';
    if (typeof fv === 'string') {
      value = fv;
    } else if (Object.prototype.toString.call(fv) === '[object Array]') {
      if (fv.length > 0) {
        fv.forEach(img => {
          let imgFile = path.join(rootFolder, img.thumb);
          let urlFile = `${global.domainHost}${img.image}`;

          if (!fs.existsSync(imgFile)) {
            imgFile = path.join(defaultImgDir, 'images/default_img.jpg');
            urlFile = `${global.domainHost}/images/default_img.jpg`;
          }

          imgObj.push({
            filename: imgFile,
            extension: 'jpeg',
            url: urlFile
          });
        });
      } else {
        value = '';
      }
    }
  });

  // 二者取其一
  if (imgObj.length > 0) {
    return imgObj;
  } else {
    return value;
  }
}

// 根目录
function getRootPath () {
  let rootFolder = '';// 根据目录
  if (process.env.NODE_ENV === 'development') { // 开发环境
    rootFolder = path.resolve();
  } else { // 生产环境
    rootFolder = path.join(path.resolve(), '..');
  }

  return rootFolder;
}

// 输出目录
function getOutputPath () {
  const rootFolder = getRootPath();
  const uploads = path.join(rootFolder, 'uploads');
  if (!fs.existsSync(uploads)) {
    fs.mkdirSync(uploads)
  }

  const outFolder = path.join(uploads, 'output'); // 输出目录
  if (!fs.existsSync(outFolder)) {
    fs.mkdirSync(outFolder)
  }
  return outFolder;
}

// 模板目录
function getTemplatePath () {
  const rootFolder = getRootPath();
  const templatePath = path.join(rootFolder, 'uploads', 'template');
  return templatePath;
}

/**
 * @function 生成单表格式的excel，即只有数据行
 * @param {object} option excel表格定义，格式如下：
 * {
 *  filename,
 *  sheets: [
 *    {
 *      label,
 *      columns: {header, field, width, style},
 *      data
 *    }
 *  ]
 * }
 * 各参数说明：
 * option.filename {string} 导出的文件名
 * option.sheets {array} sheet定义
 * option.sheets[n].label {string} sheet名称
 * option.sheets[n].columns {array} 列定义
 * option.sheets[n].columns[n].header {string} 列显示名称
 * option.sheets[n].columns[n].field {string} 字段名
 *    {}花括号或无定义为普通字段值，多字段组合在一起时使用
 *    []方括号定义为图片地址，会去取图片放到excel，有图片的行，行高为80
 *    <>尖括号定义为日期，即秒级时间戳会转成 YYYY/MM/DD
 * option.sheets[n].columns[n].width {string} 列宽
 * option.sheets[n].columns[n].style {object} 格式，参考exceljs文档 https://github.com/exceljs/exceljs/blob/HEAD/README_zh.md
 * option.sheets[n].data {array} 数据，可识别数据中的_outlineLevel，大纲级别
 * option.imageOffset.row 图片偏移行
 * option.imageOffset.col 图片偏移列
 * @param {function} cb cb(workbook, sheet, sheetname) 回调，把worksheet传出，做附加操作，回调函数需要用 new Promise()
 */
async function createExcelByDataset (option, cb) {
  const rootFolder = getRootPath();
  const outFolder = getOutputPath(); // 输出目录

  const filename = `${option.filename || 'file'}_${moment().format('YYYYMMDD')}.xlsx`;
  const excelpath = path.join(outFolder, filename);

  // 初始化
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'moduoyu';
  workbook.lastModifiedBy = 'moduoyu';
  workbook.created = new Date();
  workbook.modified = new Date();

  // 定义sheet
  const { sheets } = option;
  // 创建多sheet
  for (const sheet of sheets) {
    const worksheet = workbook.addWorksheet(sheet.label);
    worksheet.columns = sheet.columns;

    if (sheet.data && sheet.data.length > 0) {
      let rowIndex = 1;
      for (const row of sheet.data) {
        // 解析数据行
        const rowData = [];
        let hasImg = false;
        sheet.columns.forEach((col, colIndex) => {
          const fieldValue = getValue(row, col.field);

          if (typeof fieldValue === 'string' || typeof fieldValue === 'number') {
            rowData.push(fieldValue);
          } else if (Object.prototype.toString.call(fieldValue) === '[object Array]') {
            rowData.push('');
            fieldValue.forEach((img, idx) => {
              const sizeObj = sizeOf(img.filename);
              const wbImg = workbook.addImage(img);
              const imgWidth = 50;
              worksheet.addImage(wbImg, {
                tl: { col: colIndex + 0.1 + ((1 / fieldValue.length) * idx) + (option.imageOffset && option.imageOffset.col || 0), row: rowIndex + 0.1 + (option.imageOffset && option.imageOffset.row || 0) },
                ext: { width: imgWidth, height: imgWidth * sizeObj.height / sizeObj.width },
                hyperlinks: {
                  hyperlink: img.url,
                  tooltip: img.url
                }
              });
            });
            hasImg = true;
          } else {
            rowData.push(JSON.stringify(fieldValue));
          }
        });
        rowIndex++;

        // 增加数据行
        const sheetRow = worksheet.addRow(rowData);
        if (hasImg) {
          sheetRow.height = 30; // 有图片的行高
        }
        // 设置大纲
        if (row._outlineLevel) {
          sheetRow.outlineLevel = row._outlineLevel;
          sheetRow.eachCell({ includeEmpty: true }, function (cell, colNumber) {
            cell.font = {
              italic: true,
              size: 11 - row._outlineLevel
            }
          });
          sheetRow.commit();
        }
      }
    }
    // 大纲属性
    worksheet.properties.outlineProperties = {
      summaryBelow: false, // 展开按钮显示在上方
      summaryRight: false
    };

    // 边框
    worksheet.eachRow(function (row, rowNumber) {
      row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
        cell.border = {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        };
        cell.alignment = { vertical: 'middle' };
      });
      row.commit();
    });

    // 回调，把worksheet传出，做附加操作，回调函数需要用 new Promise()
    if (cb && typeof cb === 'function') {
      await cb(workbook, worksheet, sheet.label);
    }
  }

  await workbook.xlsx.writeFile(excelpath);
  return {
    url: path.join('/uploads/output', filename),
    file: filename
  };
}

/* 复制新有sheet的内容到新sheet里 */
function copySheet (fromSheet, toSheet) {
  if (fromSheet && toSheet) {
    fromSheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        toSheet.getCell(rowNumber, colNumber).value = cell.value; // 值
        toSheet.getCell(rowNumber, colNumber).style = cell.style; // 样式
        toSheet.getCell(rowNumber, colNumber)._value = cell._value; // 合并单格
        // console.log(cell._value);
      });
      const toRow = toSheet.getRow(rowNumber);
      toRow.height = row.height;
      // console.log(toRow.height, row.height);
      // toRow.hidden = row.hidden;
      toRow.commit();
    });
  }
}
/**
 * @function 自定义格式excel生成
 * @param {object} option excel表格定义，格式如下：
 * {
 *  fromFile,
 *  filename,
 *  sheets: [
 *    {
 *      label,
 *      columns,
 *      data
 *    }
 *  ]
 * }
 * 各参数说明：
 * option.fromFile {string} 导入的文件名，非必填，若无值表示全新创建，若有值表示计入该文件
 * option.filename {string} 导出的文件名
 * option.sheets {array} sheet定义
 * option.sheets[n].label {string} sheet名称
 * option.sheets[n].columns {array} 定义列宽 [{width: 10}...]
 * option.sheets[n].fromSheet {string} 先取模板文件的模板sheet做为本次模板的sheet
 * option.sheets[n].data {array} 数据，格式为[{locate, value, type, format: {numFmt, font, alignment, border, fill, line: [{index, height, outlineLevel}]}, merge, height}]
 * 说明如下：
 *    option.sheets[n].data[n].locate {String} 单元格位置或是行号
 *    option.sheets[n].data[n].value {String, Number, Array} 值，与type搭配使用 formula的形式为{ formula: 'A1+A2', result: 7 }
 *    option.sheets[n].data[n].type 值类型
 *                  default:即value为值本身或是formula
 *                  row: 数据行
 *                  image: 图片
 *    option.sheets[n].data[n].format 格式
 *    option.sheets[n].data[n].format.numFmt 数字格式 例如：'0.00%'，'# ?/?'，'dd/mm/yyyy'
 *    option.sheets[n].data[n].format.font 字体{
 *                                                name: 'Comic Sans MS',
 *                                                family: 4,
 *                                                size: 16,
 *                                                underline: true,
 *                                                 bold: true
 *                                               };
 *    option.sheets[n].data[n].format.alignment 对齐 { vertical: 'top', horizontal: 'left' };
 *    option.sheets[n].data[n].format.border 边框 {  top: {style:'thin'},  left: {style:'thin'},  bottom: {style:'thin'},  right: {style:'thin'}}
 *    option.sheets[n].data[n].format.fill 填充 {  type: 'pattern',  pattern:'darkVertical',  fgColor:{argb:'FFFF0000'}}
 *    option.sheets[n].data[n].format.line 设置行格式
 *    option.sheets[n].data[n].format.line[n].index 行索引
 *    option.sheets[n].data[n].format.line[n].height 行高
 *    option.sheets[n].data[n].format.line[n].line 大纲级别
 *    option.sheets[n].data[n].merge {array} 合并单元格，四个数值的数组 3种形式,1.一系列单元格'A4:B5' 2.按左上，右下['K10', 'M12'] 3. [开始行，开始列，结束行，结束列]
 *    option.sheets[n].data[n].height {array} 行高，[行索引,行高,行索引,行高]
 * @param {function} cb cb(book, sheet, sheetname) 回调，把worksheet传出，做附加操作，回调函数需要用 new Promise()
 */
async function createExcelByDefine (option, cb) {
  const rootFolder = getRootPath();
  const inFolder = getTemplatePath(); // 输入目录
  const outFolder = getOutputPath(); // 输出目录

  const excelFilename = `${option.filename || shortid.generate}_${moment().format('YYYYMMDD')}.xlsx`;
  const fromFile = option.fromFile && path.join(inFolder, option.fromFile);
  const excelpath = path.join(outFolder, excelFilename);

  const workbook = new ExcelJS.Workbook();

  if (option.fromFile && fs.existsSync(fromFile)) { // 有模板文件，则读取
    const templateFile = path.join(inFolder, option.fromFile);
    await workbook.xlsx.readFile(templateFile);
  } else { // 无模板文件则初始化属性
    workbook.creator = 'moduoyu';
    workbook.lastModifiedBy = 'moduoyu';
    workbook.created = new Date();
    workbook.modified = new Date();
  }

  // sheet定义
  const { sheets } = option;
  for (const sheet of sheets) {
    // 有sheet则读取，无则创建
    let worksheet = workbook.getWorksheet(sheet.label);
    // console.log('worksheet', worksheet.columnCount);
    // console.log('column width', worksheet.getColumn(1).width);
    if (!worksheet) {
      worksheet = workbook.addWorksheet(sheet.label);
      if (sheet.columns) worksheet.columns = sheet.columns; // 定义列宽

      // 从模板复制已有内容
      if (sheet.fromSheet) {
        const fromSheet = workbook.getWorksheet(sheet.fromSheet);
        copySheet(fromSheet, worksheet); // 复制sheet内容
        // workbook.removeWorksheet(sheet.fromSheet);
      }
    }
    // console.log(worksheet.getCell('A1')._row.height, worksheet.getCell('A1')._column.width);
    // 读取单元格定义
    if (sheet.data && sheet.data.length > 0) {
      for (const item of sheet.data) {
        // 格式
        const { numFmt, font, alignment, border, fill, row, column } = item.format || {};
        if (numFmt) worksheet.getCell(item.locate).numFmt = numFmt;
        if (font) worksheet.getCell(item.locate).font = font;
        if (alignment) worksheet.getCell(item.locate).alignment = alignment;
        if (border) worksheet.getCell(item.locate).border = border;
        if (fill) worksheet.getCell(item.locate).fill = fill;
        if (row) {
          for (const lineItem of row) {
            const sheetRow = worksheet.getRow(lineItem.index);
            if (lineItem.height !== undefined) sheetRow.height = lineItem.height;
            if (lineItem.outlineLevel !== undefined) sheetRow.outlineLevel = lineItem.outlineLevel;
          }
        }
        if (column) {
          for (const col of column) {
            const sheetCol = worksheet.getColumn(col.index);
            if (col.width !== undefined) sheetCol.width = col.width;
          }
        }

        // 赋值
        let imgID;
        switch (item.type) {
          case 'row':
            if (item.locate) { // 有行索引，则插入
              worksheet.insertRow(item.locate, item.value).commit(); ;
            } else { // 无行索引，则加到尾部
              worksheet.addRows(item.value).commit(); ;
            }
            break;
          case 'image':
            imgID = workbook.addImage(item.value); // 图片ID
            // console.log(imgID, item.locate);
            worksheet.addImage(imgID, item.locate);
            break;
          default:
            // console.log(item.locate, item.value);
            if (item.value !== undefined) {
              worksheet.getCell(item.locate).value = item.value || '';
            }
        }

        // 合并单元格
        if (item.merge) {
          let mergeInfo;
          try {
            for (const mergeItem of item.merge) {
              mergeInfo = mergeItem;
              if (typeof mergeItem === 'string') {
                worksheet.mergeCells(mergeItem);
              } else if (Object.prototype.toString.call(mergeItem) === '[object Array]') {
                if (mergeItem.length === 2) {
                  worksheet.mergeCells(mergeItem[0], mergeItem[1]);
                } else if (mergeItem.length === 4) {
                  worksheet.mergeCells(mergeItem[0], mergeItem[1], mergeItem[2], mergeItem[3]);
                }
              }
              // worksheet.eachRow(row => { row.commit() });
            }
          } catch (e) {
            console.log(e, mergeInfo);
          }
        }
      }
    }

    // 大纲属性
    worksheet.properties.outlineProperties = {
      summaryBelow: false, // 展开按钮显示在上方
      summaryRight: false
    };

    // 回调，把worksheet传出，做附加操作，回调函数需要用 new Promise()
    if (cb && typeof cb === 'function') {
      await cb(workbook, worksheet, sheet.label);
    }
  }

  // 删除模板sheet
  if (sheets.filter(v => v.fromSheet).length > 0) {
    [...new Set(sheets.filter(v => v.fromSheet).map(v => v.fromSheet))].forEach(item => {
      workbook.removeWorksheet(item);
    });
  }

  await workbook.xlsx.writeFile(excelpath);

  return {
    url: path.join('/uploads/output', excelFilename),
    file: excelFilename
  };
}

/**
 * @function 插入图片，excel的行高单位是磅，与英寸的换算关系 72磅=1英寸=2.54厘米
 * @param {Array} images 数据表里的图片字段内容 例如
 * [
 *   {
 *     "batch" : "7ec01cce-b961-4538-8aac-75a364377366",
 *     "idx" : 0,
 *     "image" : "/uploads/202107/upload_2c4d7b889bfe89f839a78c1a070f80ac.jpg",
 *     "thumb" : "/uploads/202107/thumb/upload_2c4d7b889bfe89f839a78c1a070f80ac.jpg"
 *    }
 * ]
 * @param {String} direction 多个图片摆放的方向，h-水平 v-垂直
 * @param {Object} imageSizeLimit { width, height } excel里图片的尺寸要求，单位 厘米
 * @param {Number} referSize 参考尺寸，单位 厘米，多个图片时，用于计算图片摆放的偏移量，合并单元格看未合并时尺寸，水平方向为待插入图片单元格的宽度，垂直方向为待插入图片单元格的高度，
 * @param {Object} position 插入图片的行列 { col, row} 索引从0开始
 * @param {Array} stack 待插入队列
 * */
function addImage (images, direction, imageSizeLimit, referSize, position, stack) {
  const attachRoot = tools.attachRootPath();
  const { col, row } = position;
  let offset = 0;
  for (let i = 0; i < images.length; i++) {
    const img = images[i];
    const imgPath = path.join(attachRoot, img.image);

    if (fs.existsSync(imgPath)) {
      const sizeObj = sizeOf(imgPath);
      const fileType = sizeObj.type.toLowerCase();
      if (['jpg', 'png', 'gif'].includes(fileType)) {
        const limitRatio = imageSizeLimit.width / imageSizeLimit.height;
        const imageRatio = sizeObj.width / sizeObj.height;
        let width, height;
        if (limitRatio > imageRatio) {
          height = imageSizeLimit.height / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          width = sizeObj.width * height / sizeObj.height;
        } else {
          width = imageSizeLimit.width / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          height = sizeObj.height * width / sizeObj.width;
        }
        const extension = fileType === 'jpg' ? 'jpeg' : sizeObj.type;

        if (direction === 'h') {
          // const height = imageSizeLimit / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          // const width = sizeObj.width * height / sizeObj.height;
          stack.push({
            locate: {
              tl: { col: col + offset * i, row },
              ext: { width, height }
            },
            value: { filename: imgPath, extension },
            type: 'image'
          });
          offset = width / (referSize / 2.54 * 96);
        } else {
          // const width = imageSizeLimit / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          // const height = sizeObj.height * width / sizeObj.width;
          stack.push({
            locate: {
              tl: { col, row: row + offset * i },
              ext: { width, height }
            },
            value: { filename: imgPath, extension },
            type: 'image'
          });
          offset = height / (referSize / 2.54 * 96);
        }
      }
    }
  }
}

/**
 * 计算偏移量
 * @param {*} sizes 各列的宽度 或 各行的高度
 * @param {*} point 起始点
 */
function calcOffset (sizes, point) {
  // 累积数组，得到每个分割点的位置
  const cumulatedSizes = [0]; // 初始位置为0
  for (const size of sizes) {
    cumulatedSizes.push(cumulatedSizes[cumulatedSizes.length - 1] + size);
  }

  // 查找point位于哪个区间
  const segmentIndex = cumulatedSizes.findIndex((cumSize) => point < cumSize) - 1;
  if (segmentIndex < 0 || segmentIndex >= sizes.length) {
    console.error('Point is outside the range of segments.');
    return 0;
  }

  // 计算point在该段的百分比
  const startOfSegment = cumulatedSizes[segmentIndex];
  const ratioInSegment = (point - startOfSegment) / sizes[segmentIndex];

  // 偏移量增加40%
  return segmentIndex + Math.round(ratioInSegment * 140) / 100;
}

/**
 * 把图片插入sheet,多个图片按顺序计算偏移量
 * @param {String} direction 多个图片摆放的方向，h-水平 v-垂直
 * @param {Object} imageSizeLimit { width, height } excel里图片的尺寸要求，单位 厘米
 * @param {Number} referSize 参考尺寸，单位 厘米，多个图片时，用于计算图片摆放的偏移量，合并单元格看未合并时尺寸，水平方向为待插入图片单元格的宽度，垂直方向为待插入图片单元格的高度，
 * @param {Object} position 插入图片的行列 { col, row} 索引从0开始
 * @param {*} book excel的workbook
 * @param {*} sheet excel的worksheet
 */
function addImageToSheet (images, direction, imageSizeLimit, position, book, sheet) {
  const attachRoot = tools.attachRootPath();
  const { col, row } = position;
  let totalLen = 0;

  const colSize = [], rowSize = [];
  for (let i = 0; i < sheet.columnCount; i++) {
    colSize.push(sheet.getColumn(i + 1).width * 0.22862 / 2.54 * 96); // 所有列宽 列宽：1毫米＝0.4374个单位；1厘米＝4.374 个单位；1个单位＝2.2862毫米；
  }
  for (let i = 0; i < sheet.rowCount; i++) {
    rowSize.push(sheet.getRow(i + 1).height * 0.03612 / 2.54 * 96); // 所有行高 行高：1毫米＝2.7682个单位；1厘米＝27.682个单位；1个单位＝0.3612毫米；
  }

  for (const img of images) {
    const imgPath = path.join(attachRoot, img.image);

    if (fs.existsSync(imgPath)) {
      const sizeObj = sizeOf(imgPath);
      const fileType = sizeObj.type.toLowerCase();
      if (['jpg', 'png', 'gif'].includes(fileType)) {
        const limitRatio = imageSizeLimit.width / imageSizeLimit.height;
        const imageRatio = sizeObj.width / sizeObj.height;
        let width, height;
        if (limitRatio > imageRatio) {
          height = imageSizeLimit.height / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          width = sizeObj.width * height / sizeObj.height;
        } else {
          width = imageSizeLimit.width / 2.54 * 96;// 厘米 / 英寸转化率 * 96bpi/inch
          height = sizeObj.height * width / sizeObj.width;
        }
        const extension = fileType === 'jpg' ? 'jpeg' : sizeObj.type;

        const imgID = book.addImage({ filename: imgPath, extension }); // 图片ID
        if (direction === 'h') {
          const img = sheet.addImage(imgID, {
            tl: { col: col + calcOffset(colSize, totalLen), row },
            ext: { width, height }
          });
          totalLen += width;
        } else {
          sheet.addImage(imgID, {
            tl: { col, row: row + calcOffset(rowSize, totalLen) },
            ext: { width, height }
          });
          totalLen += height;
        }
      }
    }
  }
}

/**
 * 生成数据集的sheet
 * @param {*} columns 列定义，如下
 * {
 * header: '含税', // 列标题
 * field: 'tax_included', // 字段名
 * width: 6, // 列宽，excel里显示列宽的数值
 * headerFormat: { // 列标题格式定义，exceljs里的定义内容，例如下
 *   font: { bold: true }, alignment: { horizontal: 'center', vertical: 'middle' } },
 * dataFormat: // 数据单元格的格式定义，可为function，exceljs里的定义内容
 * 静态内容例如下：
 * { numFmt: '0' },
 * function内容例如下：返回object
 * (val, row) => { return { numFmt: '0' }}
 * render: // 数据的取值函数，根据需要定义即可
 *   val => global.dictObj && global.dictObj.yesNo && global.dictObj.yesNo[val].label },
 * @param {*} data 数据集
 * @param {*} label sheet名
 * @param {*} beginRow 开始输出行 默认第一行
 */
function datasetSheet (columns, data, label = null, beginRow = 1) {
  const sheet = {
    label: label || 'sheet1',
    columns: columns.map(col => { return { width: col.width } }),
    data: [
      ...columns.map((col, idx) => {
        const colNo = tools.convertDSTo26BS(idx + 1);
        const cell = { locate: `${colNo}${beginRow}`, value: col.header };
        if (col.headerFormat) {
          cell.format = col.headerFormat;
        }
        return cell;
      }),
      ...data.map((row, rowIdx) => {
        const rowNo = beginRow + 1 + rowIdx;
        return columns.map((col, colIdx) => {
          const colNo = tools.convertDSTo26BS(colIdx + 1);

          const cell = { locate: `${colNo}${rowNo}`, value: col.render ? col.render(row[col.field]) : row[col.field] };

          if (col.dataFormat) {
            if (typeof col.dataFormat === 'object') {
              if (col.dataFormat.numFmt) {
                cell.value = Number(cell.value);
                if (col.dataFormat.numFmt.indexOf('%') >= 0) {
                  cell.value = cell.value / 100;
                }
                if (cell.value === 0) {
                  cell.value = col.dataFormat.numFmt;
                }
              }
              cell.format = col.dataFormat;
            } else if (typeof col.dataFormat === 'function') {
              cell.format = col.dataFormat(row[col.field], row);
            }
          }
          return cell;
        });
      }).flat()
    ]
  };

  return sheet;
}

exports.exportDatasetExcel = createExcelByDataset; // 数据集型
exports.exportDefineExcel = createExcelByDefine; // 自定义型
exports.addImage = addImage; // 加图片
exports.addImageToSheet = addImageToSheet; // 加图片
exports.getDatasetSheet = datasetSheet; // 获取数据集的sheet
